package xyz.hlh.boot2.poitest.usermodel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.FileOutputStream;

/**
 * @author HLH
 * @description POI 大文件写
 * @email 17703595860@163.com
 * @date : Created in  2021/8/2 下午9:33
 */
public class POIBigWrite {

    private static final String PATH = "/media/hlh/13B69828E0E35204/A-IdeaProject-UOS/spring-boot-csdn/02-poi-easyExcel/src/file/";

    /**
     * xls大文件写
     *  使用 HSSFWorkbook 进行操作
     *  使用xls格式最多写入65536行数据  2.174s
     */
    @Test
    public void poi03Write() {
        // 工作簿
        try(Workbook workbook = new HSSFWorkbook();
            FileOutputStream fileOutputStream = new FileOutputStream(PATH + "03big.xls")) {
            long start = System.currentTimeMillis();

            // 工作表
            Sheet sheet = workbook.createSheet();
            // 写 65536 条数据
            for (int i = 0; i < 65536; i++) {
                // 行
                Row row = sheet.createRow(i);
                for (int j = 0; j < 50; j++) {
                    // 列
                    Cell cell = row.createCell(j);
                    cell.setCellValue(j);
                }
            }

            // 写出文件
            workbook.write(fileOutputStream);

            long end = System.currentTimeMillis();
            System.out.println(((double)end - start ) / 1000);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * xlsx 写
     *  使用 XSSFWorkbook 进行操作
     *  写入 25万 条数据 java 最高8G 空间 25万条就会内存溢出OOM
     *      20万条测试可以 66.928s
     *  单sheet最多到处1048575行
     */
    @Test
    public void poi07Write() {
        // 工作簿
        try(Workbook workbook = new XSSFWorkbook();
            FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07big.xlsx")) {
            long start = System.currentTimeMillis();

            // 工作表
            Sheet sheet1 = workbook.createSheet();
            // 写 1000000 条数据
            for (int i = 0; i < 200000; i++) {
                // 行
                Row row = sheet1.createRow(i);
                for (int j = 0; j < 50; j++) {
                    // 列
                    Cell cell = row.createCell(j);
                    cell.setCellValue(j);
                }
            }

            // 写出文件
            workbook.write(fileOutputStream);

            long end = System.currentTimeMillis();
            System.out.println(((double)end - start ) / 1000);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * xlsx 写
     *  使用 SXSSFWorkbook 进行操作(优化之后的)
     *  100万条数据，占用内存500多M，耗时43.288秒
     *  单sheet最多到处1048576行
     */
    @Test
    public void poi07WriteSXSSF() {
        /// 工作簿
        try(Workbook workbook = new SXSSFWorkbook();
            FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07big.xlsx")) {
            long start = System.currentTimeMillis();

            // 工作表
            Sheet sheet1 = workbook.createSheet();
            // 写 1000000 条数据
            for (int i = 0; i < 1000000; i++) {
                // 行
                Row row = sheet1.createRow(i);
                for (int j = 0; j < 50; j++) {
                    // 列
                    Cell cell = row.createCell(j);
                    cell.setCellValue(j);
                }
            }

            // 写出文件
            workbook.write(fileOutputStream);

            long end = System.currentTimeMillis();
            System.out.println(((double)end - start ) / 1000);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
